import frappe
from frappe.query_builder.functions import Sum, Avg
from frappe.query_builder import Case

def purchase_order_update_service_item(doc, method):
    if doc.is_subcontracted:
        item_price = frappe.qb.DocType('Item Price')
        for row in doc.items:
            if not row.item_code and row.fg_item:
                service_item_code = frappe.db.get_value('Item',row.fg_item,'service_item')
                if service_item_code:
                    service_item_info = frappe.db.get_value('Item',service_item_code,['item_code', 'item_name','description','stock_uom'])
                    row.item_code = service_item_info[0]
                    row.item_name = service_item_info[1]
                    row.description = service_item_info[2]
                    row.uom = service_item_info[3]
                    row.fg_item_qty = row.qty
                    #取fg_item的采购价
                    q = frappe.qb.from_(item_price
                        ).select(item_price.price_list_rate
                        ).where(item_price.item_code == row.fg_item
                        ).where((item_price.supplier == doc.supplier) | (item_price.supplier.isnull()) | (item_price.supplier == '')
                        ).where(item_price.buying == 1
                        ).where(item_price.price_list == doc.buying_price_list
                        ).where((item_price.valid_from <= doc.transaction_date) | (item_price.valid_from.isnull()) | (item_price.valid_from == '')
                        ).where((item_price.valid_upto >= doc.transaction_date) | (item_price.valid_upto.isnull()) | (item_price.valid_upto == '')
                        ).limit(1
                        ).run(as_dict=1)
                    if q:    
                        row.rate = q[0].get('price_list_rate')
    doc.calculate_taxes_and_totals()

def subcontracting_order_update_net_rate(doc, method):
    """标准功能未考虑含税价问题，修正为使用未税价作为加工费"""
    if doc.has_value_changed("service_items"):
        fg_item_net_rate_map = frappe._dict(
            frappe.get_all('Purchase Order Item',
                fields =['fg_item','net_rate'],
                filters = {'parent': doc.purchase_order},
                as_list = 1
            ))
        for row in doc.service_items:
            row.rate = fg_item_net_rate_map.get(row.fg_item) or row.rate
            row.amount = row.qty * row.rate
        # 重新计算委外成品成本
        doc.set_missing_values()

def subcontracting_receipt_poi_expense_account(doc, method):
    sri = frappe.qb.DocType('Subcontracting Receipt Item')
    soi = frappe.qb.DocType('Subcontracting Order Item')
    poi = frappe.qb.DocType('Purchase Order Item')
    query_result = frappe.qb.from_(sri
        ).join(soi
        ).on(sri.subcontracting_order_item == soi.name
        ).join(poi
        ).on(poi.name == soi.purchase_order_item
        ).where(sri.parent == doc.name
        ).select(poi.name, poi.expense_account
        ).run(as_list = True)
    account_map = frappe._dict(query_result)
    if account_map:
        for row in doc.items:
            expense_account = account_map.get(row.purchase_order_item)
            if expense_account:
                row.expense_account = expense_account

def update_reserved_qty_for_sub_contract(doc, method):
    """
    1. 多委外订单合并发料(委外发料不关联委外订单)
    2. 按物料清单倒扣原材料
    3. 解决委外订单中委外预留数量在委外入库后未更新问题
    """

    def update_bin_qty(item_code, warehouse, qty):
        bin = frappe.db.get_value("Bin", {"item_code": item_code, "warehouse": warehouse})
        if bin:
            bin_obj = frappe.get_doc("Bin", bin, for_update=True)
            bin_obj.flags.ignore_permissions = True
            bin_obj.db_set("reserved_qty_for_sub_contract", qty, update_modified=True)
            bin_obj.set_projected_qty()
            bin_obj.db_set("projected_qty", bin_obj.projected_qty, update_modified=True)

    # 获取委外订单
    doctype = doc.doctype
    if doctype == 'Stock Entry' and doc.subcontracting_order:
        subcontracting_orders = [doc.subcontracting_order]
    elif doctype == "Subcontracting Order":
        subcontracting_orders = [doc.name]
    elif doctype == "Subcontracting Receipt":
        subcontracting_orders = {row.subcontracting_order for row in doc.items}
    else:
        return
        
    so = frappe.qb.DocType("Subcontracting Order")
    sosi = frappe.qb.DocType("Subcontracting Order Supplied Item")
    sosi_1 = frappe.qb.DocType("Subcontracting Order Supplied Item").as_('sosi_1')
    
    available_qty = Case(
        ).when(
            (
                (so.status.isin(['Completed','Closed'])) |
                (so.per_received >= 100) |
                (sosi.required_qty <= (sosi.supplied_qty - sosi.returned_qty)) |
                (sosi.required_qty <= sosi.consumed_qty)
            )
            , sosi.required_qty            
        ).when((sosi.supplied_qty - sosi.returned_qty) > sosi.consumed_qty, 
            (sosi.supplied_qty - sosi.returned_qty)
        ).else_(
            sosi.consumed_qty
        )

    #当前委外订单所有委外原料
    sub_query_rm_item_code = frappe.qb.from_(sosi_1
    ).select(sosi_1.rm_item_code
    ).distinct(
    ).where(sosi_1.parent.isin(subcontracting_orders))

    query = frappe.qb.from_(so
    ).join(sosi
    ).on(so.name == sosi.parent
    ).select(
        sosi.rm_item_code,
        sosi.reserve_warehouse,
        (Sum(sosi.required_qty) - Sum(available_qty)).as_('qty')
    ).where(        
        (so.docstatus == 1) &
        (sosi.rm_item_code.isin(sub_query_rm_item_code))
    ).groupby(
        sosi.rm_item_code,
        sosi.reserve_warehouse
    )

    qty_list = query.run(as_dict=1)

    for row in qty_list:
        update_bin_qty(row.rm_item_code, row.reserve_warehouse, 0 if row.qty<0 else row.qty)